package DAO;

import java.sql.*;
import java.util.ArrayList;

/**
 * Contains the functions that contains the sql queries that can manipulate and access the database
 * @author Jason, Joshua and Sam
 */
public class MySQLFlightDAO implements FlightDAO {

    private ResultSet rs;
    private Statement stmt;

    /**
     * Preforms a very complex sql query and adds the results to the JPanel
     * @return JPanel
     */
    public ArrayList getFlightsInfo() {
        MySQLDAOFactory getFlightsFactory = new MySQLDAOFactory();
        String departure = null, destination = null, dep_time = null, arr_time = null;

        ArrayList<ArrayList<String>> list2 = new ArrayList<ArrayList<String>>();
        //JPanel flightInfoPanel = new JPanel();
        //flightInfoPanel.setLayout(new BoxLayout(flightInfoPanel, BoxLayout.Y_AXIS));
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT DEP.Departs AS Departs, DEP.Time AS DepartTime, DEP.Date AS DepartDate, ARR.Arrives AS Arrives, ARR.Time AS ArriveTime, ARR.Date AS ArriveDate, FLIGHT_LEG.SEATS_AVAILABLE AS Seats, TICKET.PRICE AS Price " +
                    "FROM " +
                    "(SELECT AP.AIRPORT_LOCATION AS Departs, D.TIME AS Time, D.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, DEPARTURE AS D, AIRPORT AS AP " +
                    "WHERE FL.DEPART_ID = D.DEPART_ID " +
                    "AND D.AIRPORT_ID = AP.AIRPORT_ID) AS DEP, " +
                    "(SELECT AP.AIRPORT_LOCATION AS Arrives, A.TIME AS Time, A.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, ARRIVAL AS A, AIRPORT AS AP " +
                    "WHERE FL.ARRIVE_ID = A.ARRIVE_ID " +
                    "AND A.AIRPORT_ID = AP.AIRPORT_ID) AS ARR, FLIGHT_LEG, FLIGHT, TICKET " +
                    "WHERE DEP.Leg = ARR.Leg " +
                    "AND ARR.Leg = FLIGHT_LEG.LEG_ID " +
                    "AND FLIGHT.FLIGHT_ID = FLIGHT_LEG.FLIGHT_ID " +
                    "AND TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID; ");
            while (rs.next()) {

                ArrayList<String> list1 = new ArrayList<String>();
                list1.add((rs.getString("Departs")));
                list1.add((rs.getString("DepartDate")));
                list1.add((rs.getString("DepartTime")));
                list1.add((rs.getString("Arrives")));
                list1.add((rs.getString("ArriveDate")));
                list1.add((rs.getString("ArriveTime")));
                list1.add((rs.getString("Seats")));
                list1.add((rs.getString("Price")));
                list2.add(list1);


            //JTable flightTable = new JTable();
            //flightTable.addColumn(new TableColumn("Departure:"));

            /* flightTable.addColumn(new TableColumn("Departure:"));
            JPanel flightPanel = new JPanel();
            JPanel departurePanel = new JPanel();
            JLabel departureLabel = new JLabel("Departure: ");
            JLabel departureInfoLabel = new JLabel();

            JPanel departureDatePanel = new JPanel();
            JLabel departureDateLabel = new JLabel("Departure Date: ");
            JLabel departureDateInfoLabel = new JLabel();

            JPanel departureTimePanel = new JPanel();
            JLabel departureTimeLabel = new JLabel("Departure Time: ");
            JLabel departureTimeInfoLabel = new JLabel();

            JPanel destinationPanel = new JPanel();
            JLabel destinationLabel = new JLabel("Destination: ");
            JLabel destinationInfoLabel = new JLabel();

            JPanel arrivalDatePanel = new JPanel();
            JLabel arrivalDateLabel = new JLabel("Arrival Date: ");
            JLabel arrivalDateInfoLabel = new JLabel();

            JPanel arrivalTimePanel = new JPanel();
            JLabel arrivalTimeLabel = new JLabel("Arrival Time: ");
            JLabel arrivalTimeInfoLabel = new JLabel();

            JPanel seatsAvailablePanel = new JPanel();
            JLabel seatsAvailableLabel = new JLabel("Seats Available: ");
            JLabel seatsAvailableInfoLabel = new JLabel();

            JPanel pricePanel = new JPanel();
            JLabel priceLabel = new JLabel("Price: $");
            JLabel priceInfoLabel = new JLabel();

            JButton editFlightDetails = new JButton("Edit");


            departureInfoLabel.setText(rs.getString("Departs"));
            departureDateInfoLabel.setText(rs.getString("DepartDate"));
            departureTimeInfoLabel.setText(rs.getString("DepartTime"));
            destinationInfoLabel.setText(rs.getString("Arrives"));
            arrivalDateInfoLabel.setText(rs.getString("ArriveDate"));
            arrivalTimeInfoLabel.setText(rs.getString("ArriveTime"));
            seatsAvailableInfoLabel.setText(rs.getString("Seats"));
            priceInfoLabel.setText(rs.getString("Price"));

            flightPanel.add(departurePanel);
            departurePanel.add(departureLabel);
            departurePanel.add(departureInfoLabel);

            flightPanel.add(departureDatePanel);
            departureTimePanel.add(departureDateLabel);
            departureTimePanel.add(departureDateInfoLabel);

            flightPanel.add(departureTimePanel);
            departureTimePanel.add(departureTimeLabel);
            departureTimePanel.add(departureTimeInfoLabel);

            flightPanel.add(destinationPanel);
            destinationPanel.add(destinationLabel);
            destinationPanel.add(destinationInfoLabel);

            flightPanel.add(arrivalDatePanel);
            arrivalTimePanel.add(arrivalDateLabel);
            arrivalTimePanel.add(arrivalDateInfoLabel);

            flightPanel.add(arrivalTimePanel);
            arrivalTimePanel.add(arrivalTimeLabel);
            arrivalTimePanel.add(arrivalTimeInfoLabel);

            flightPanel.add(seatsAvailablePanel);
            seatsAvailablePanel.add(seatsAvailableLabel);
            seatsAvailablePanel.add(seatsAvailableInfoLabel);

            flightPanel.add(pricePanel);
            pricePanel.add(priceLabel);
            pricePanel.add(priceInfoLabel);

            flightPanel.add(editFlightDetails);
            flightPanel.setVisible(true);

            flightInfoPanel.add(flightPanel);
             */
            }
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        //flightInfoPanel.setVisible(true);
        //return flightInfoPanel;
        return list2;
    }

    public ArrayList searchByDepart(String depart) {
        MySQLDAOFactory getFlightsFactory = new MySQLDAOFactory();
        String departure = null, destination = null, dep_time = null, arr_time = null;

        ArrayList<ArrayList<String>> list2 = new ArrayList<ArrayList<String>>();

        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT DEP.Departs AS Departs, DEP.Time AS DepartTime, DEP.Date AS DepartDate, ARR.Arrives AS Arrives, ARR.Time AS ArriveTime, ARR.Date AS ArriveDate, FLIGHT_LEG.SEATS_AVAILABLE AS Seats, TICKET.PRICE AS Price " +
                    "FROM " +
                    "(SELECT AP.AIRPORT_LOCATION AS Departs, D.TIME AS Time, D.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, DEPARTURE AS D, AIRPORT AS AP " +
                    "WHERE FL.DEPART_ID = D.DEPART_ID " +
                    "AND D.AIRPORT_ID = AP.AIRPORT_ID) AS DEP, " +
                    "(SELECT AP.AIRPORT_LOCATION AS Arrives, A.TIME AS Time, A.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, ARRIVAL AS A, AIRPORT AS AP " +
                    "WHERE FL.ARRIVE_ID = A.ARRIVE_ID " +
                    "AND A.AIRPORT_ID = AP.AIRPORT_ID) AS ARR, FLIGHT_LEG, FLIGHT, TICKET " +
                    "WHERE DEP.Leg = ARR.Leg " +
                    "AND ARR.Leg = FLIGHT_LEG.LEG_ID " +
                    "AND FLIGHT.FLIGHT_ID = FLIGHT_LEG.FLIGHT_ID " +
                    "AND TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID; " +
                    "AND DEP.Departs = '" + depart + "';");

            while (rs.next()) {
                ArrayList<String> list1 = new ArrayList<String>();
                list1.add((rs.getString("Departs")));
                list1.add((rs.getString("DepartDate")));
                list1.add((rs.getString("DepartTime")));
                list1.add((rs.getString("Arrives")));
                list1.add((rs.getString("ArriveDate")));
                list1.add((rs.getString("ArriveTime")));
                list1.add((rs.getString("Seats")));
                list1.add((rs.getString("Price")));
                list2.add(list1);
            }
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        return list2;
    }

    public ArrayList searchByArrive(String arrive) {
        MySQLDAOFactory getFlightsFactory = new MySQLDAOFactory();
        String departure = null, destination = null, dep_time = null, arr_time = null;

        ArrayList<ArrayList<String>> list2 = new ArrayList<ArrayList<String>>();

        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT DEP.Departs AS Departs, DEP.Time AS DepartTime, DEP.Date AS DepartDate, ARR.Arrives AS Arrives, ARR.Time AS ArriveTime, ARR.Date AS ArriveDate, FLIGHT_LEG.SEATS_AVAILABLE AS Seats, TICKET.PRICE AS Price " +
                    "FROM " +
                    "(SELECT AP.AIRPORT_LOCATION AS Departs, D.TIME AS Time, D.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, DEPARTURE AS D, AIRPORT AS AP " +
                    "WHERE FL.DEPART_ID = D.DEPART_ID " +
                    "AND D.AIRPORT_ID = AP.AIRPORT_ID) AS DEP, " +
                    "(SELECT AP.AIRPORT_LOCATION AS Arrives, A.TIME AS Time, A.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, ARRIVAL AS A, AIRPORT AS AP " +
                    "WHERE FL.ARRIVE_ID = A.ARRIVE_ID " +
                    "AND A.AIRPORT_ID = AP.AIRPORT_ID) AS ARR, FLIGHT_LEG, FLIGHT, TICKET " +
                    "WHERE DEP.Leg = ARR.Leg " +
                    "AND ARR.Leg = FLIGHT_LEG.LEG_ID " +
                    "AND FLIGHT.FLIGHT_ID = FLIGHT_LEG.FLIGHT_ID " +
                    "AND TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID; " +
                    "AND ARR.Arrives = '" + arrive + "';");
            while (rs.next()) {

                ArrayList<String> list1 = new ArrayList<String>();
                list1.add((rs.getString("Departs")));
                list1.add((rs.getString("DepartDate")));
                list1.add((rs.getString("DepartTime")));
                list1.add((rs.getString("Arrives")));
                list1.add((rs.getString("ArriveDate")));
                list1.add((rs.getString("ArriveTime")));
                list1.add((rs.getString("Seats")));
                list1.add((rs.getString("Price")));
                list2.add(list1);
            }
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        return list2;
    }

    public ArrayList searchByDate(String date) {
        MySQLDAOFactory getFlightsFactory = new MySQLDAOFactory();
        String departure = null, destination = null, dep_time = null, arr_time = null;

        ArrayList<ArrayList<String>> list2 = new ArrayList<ArrayList<String>>();

        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT DEP.Departs AS Departs, DEP.Time AS DepartTime, DEP.Date AS DepartDate, ARR.Arrives AS Arrives, ARR.Time AS ArriveTime, ARR.Date AS ArriveDate, FLIGHT_LEG.SEATS_AVAILABLE AS Seats, TICKET.PRICE AS Price " +
                    "FROM " +
                    "(SELECT AP.AIRPORT_LOCATION AS Departs, D.TIME AS Time, D.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, DEPARTURE AS D, AIRPORT AS AP " +
                    "WHERE FL.DEPART_ID = D.DEPART_ID " +
                    "AND D.AIRPORT_ID = AP.AIRPORT_ID) AS DEP, " +
                    "(SELECT AP.AIRPORT_LOCATION AS Arrives, A.TIME AS Time, A.DATE AS Date, FL.LEG_ID AS Leg " +
                    "FROM FLIGHT_LEG AS FL, ARRIVAL AS A, AIRPORT AS AP " +
                    "WHERE FL.ARRIVE_ID = A.ARRIVE_ID " +
                    "AND A.AIRPORT_ID = AP.AIRPORT_ID) AS ARR, FLIGHT_LEG, FLIGHT, TICKET " +
                    "WHERE DEP.Leg = ARR.Leg " +
                    "AND ARR.Leg = FLIGHT_LEG.LEG_ID " +
                    "AND FLIGHT.FLIGHT_ID = FLIGHT_LEG.FLIGHT_ID " +
                    "AND TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID; " +
                    "AND DEP.Date = '" + date + "';");
            while (rs.next()) {

                ArrayList<String> list1 = new ArrayList<String>();
                list1.add((rs.getString("Departs")));
                list1.add((rs.getString("DepartDate")));
                list1.add((rs.getString("DepartTime")));
                list1.add((rs.getString("Arrives")));
                list1.add((rs.getString("ArriveDate")));
                list1.add((rs.getString("ArriveTime")));
                list1.add((rs.getString("Seats")));
                list1.add((rs.getString("Price")));
                list2.add(list1);
            }
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        return list2;
    }

    public void addFlight(String depart, String arrive, String departDate, String arriveDate, String departTime, String arriveTime, int numSeats, float price) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            stmt.executeUpdate("INSERT INTO `FLIGHT_LEG` VALUES ( '1', '1', '1', '1', " + numSeats + " );");
            stmt.executeUpdate("INSERT INTO `FLIGHT` VALUES ( 'A' );");
            stmt.executeUpdate("INSERT INTO `DEPARTURE` VALUES ('(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + depart + ")', '7', '" + departTime + "', '" + departDate + "' );");
            stmt.executeUpdate("INSERT INTO `ARRIVAL` VALUES ('(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + arrive + ")', '5', '" + arriveTime + "', '" + arriveDate + "' );");
            stmt.executeUpdate("INSERT INTO `TICKET` VALUES ( '1', '1', '" + price + "', '" + price * 0.1 + "');");
            System.out.println("Flight Inserted");
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
    }

    public void editFlight(String depart, String arrive, String departDate, String arriveDate, String departTime, String arriveTime, int numSeats, float price) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            stmt.executeUpdate("UPDATE `FLIGHT_LEG` SET SEATS_AVAILABLE = " + numSeats + " WHERE");
            stmt.executeUpdate("UPDATE `DEPARTURE` SET DEPART_ID = '(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + depart + ")', TIME = '" + departTime + "', DATE = '" + departDate + "' WHERE;");
            stmt.executeUpdate("UPDATE `ARRIVAL` SET ARRIVE_ID = '(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + arrive + ")', TIME = '" + arriveTime + "', DATE = '" + arriveDate + "' WHERE;");
            stmt.executeUpdate("UPDATE `TICKET` SET  PRICE = " + price + ", TAX = " + price * 0.1 + "WHERE;");
            System.out.println("Flight Updated");
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
    }

    public void deleteFlight(String depart, String arrive, String departDate, String arriveDate, String departTime, String arriveTime, int numSeats, float price) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            stmt.executeUpdate("DELETE FROM `FLIGHT_LEG` WHERE SEATS_AVAILABLE = " + numSeats + ";");
            stmt.executeUpdate("DELETE FROM `DEPARTURE` WHERE DEPART_ID = '(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + depart + ")' AND WHERE TIME = '" + departTime + "' AND WHERE DATE = '" + departDate + ";");
            stmt.executeUpdate("DELETE FROM `ARRIVAL` WHERE ARRIVE_ID = '(SELECT AIRPORT_ID FROM AIRPORT WHERE AIRPORT_NAME = " + arrive + ")', TIME = '" + arriveTime + "', DATE = '" + arriveDate + ";");
            stmt.executeUpdate("DELETE FROM `TICKET` WHERE  PRICE = " + price + ";");
            System.out.println("Flight Deleted");
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
    }
}
